
ALTER    proc sp_booking_quarter_report 

as
begin
	declare @results Table(LineItmNo int, ProductID int, CoolQty int,CoolUnitCost float,CoolAmount float,CoolReqDate datetime, 
				CoolBkDate datetime, CoolSchdDate datetime, CoolRevDate datetime, CoolStatus varchar(20), SoNo int, 
				CustID int, SoPoNo varchar (20), SoPerson varchar(20), SoEndUser varchar(65), SoBkDate datetime, 
				PartNo varchar(20), CustName varchar(65), CustRegion varchar(20),CustRepOffice varchar(30), Qtr int, Yr int,Mth int)
	
	declare @lineItmNo int, @productID int, @coolQty int
	declare @coolUnitCost float, @coolAmount float
	declare @coolReqDate datetime, @coolBkDate datetime, @coolSchdDate datetime, @coolRevDate datetime
	declare @coolStatus varchar(20)
	declare @soNo int, @custID int
	declare @soPoNo varchar (20), @soPerson varchar(20), @soEndUser varchar(65)
	declare @soBkDate datetime
	declare @partNo varchar(20), @custName varchar(65), @custRegion varchar(20), @custRepOffice varchar(30)
	declare @month int, @year int, @quarter int
	
	declare mainCursor cursor for
	SELECT
		Customer_Open_Order_LineItem.COOL_Line_Item_No, 
		Customer_Open_Order_LineItem.Product_ID, 
		Customer_Open_Order_LineItem.COOL_Qty, 
		Customer_Open_Order_LineItem.COOL_Unit_Cost, 
		Customer_Open_Order_LineItem.COOL_Amount, 
		Customer_Open_Order_LineItem.COOL_Requested_Date, 
		Customer_Open_Order_LineItem.COOL_Booking_Date, 
		Customer_Open_Order_LineItem.COOL_Schedule_Date, 
		Customer_Open_Order_LineItem.COOL_Revised_Date, 
		Customer_Open_Order_LineItem.COOL_Status,
		Sales_Order.SalesOrder_No, 
		Sales_Order.Customer_ID, 
		Sales_Order.SO_Customer_PO_Number, 
		Sales_Order.SO_Sales_Person, 
		Sales_Order.SO_EndUser, 
		Sales_Order.SO_Booking_Date,
		Product.P_Marketing_Part_Number,
		Customer.C_Name, 
		Customer.C_Region, 
		Customer.C_Rep_Office
	FROM
	     ((Customer_Open_Order_LineItem INNER JOIN Product ON
	        Customer_Open_Order_LineItem.Product_ID = Product.Product_ID)
	     INNER JOIN Sales_Order ON Customer_Open_Order_LineItem.SalesOrder_ID = Sales_Order.SalesOrder_ID)
	     INNER JOIN Customer ON Sales_Order.Customer_ID = Customer.Customer_ID 

		open mainCursor
		fetch next from mainCursor into 
		@lineItmNo, @productID, @coolQty,@coolUnitCost, @coolAmount,@coolReqDate, @coolBkDate, 
		@coolSchdDate, @coolRevDate,@coolStatus,@soNo, @custID,@soPoNo, @soPerson, @soEndUser, 
		@soBkDate,@partNo, @custName, @custRegion, @custRepOffice 
		while @@fetch_status = 0
		begin
			if @soBkDate is not null
			begin
				
				set @month = DATEPART(m,@soBkDate)
				print @month
				set @year=DATEPART(yyyy,@soBkDate)
				print @year
				if @coolStatus = 'PENDING'
					begin
						set @coolStatus = 'HOLD'
					end
				-- Modified by Prabhanjan (14 Apr 2004)
				--if  @month >=2 and @month <=4
				if  @month >=1 and @month <=3
				--if  @month between 2 and 5
				begin
					set @quarter = 1
					--set @month = 4
					set @month = 3
					insert into @results values(@lineItmNo, @productID, @coolQty,@coolUnitCost, @coolAmount,@coolReqDate, @coolBkDate, 
					@coolSchdDate, @coolRevDate,@coolStatus,@soNo, @custID,@soPoNo, @soPerson, @soEndUser, 
					@soBkDate,@partNo, @custName, @custRegion, @custRepOffice, @quarter,@year,@month)
					
				end
				--else if  @month >=5 and @month <=7
				else if  @month >=4 and @month <=6
				--else if  @month between 5 and 8
				begin
					set @quarter = 2
					--set @month = 7
					set @month = 6
					insert into @results values(@lineItmNo, @productID, @coolQty,@coolUnitCost, @coolAmount,@coolReqDate, @coolBkDate, 
					@coolSchdDate, @coolRevDate,@coolStatus,@soNo, @custID,@soPoNo, @soPerson, @soEndUser, 
					@soBkDate,@partNo, @custName, @custRegion, @custRepOffice, @quarter,@year,@month)
					
				end
				--else if  @month >=8 and @month <=10
				else if  @month >=7 and @month <=9
				--else if  @month between 8 and 11
				begin
					print @month
					set @quarter = 3
					--set @month = 10
					set @month = 9
					insert into @results values(@lineItmNo, @productID, @coolQty,@coolUnitCost, @coolAmount,@coolReqDate, @coolBkDate, 
					@coolSchdDate, @coolRevDate,@coolStatus,@soNo, @custID,@soPoNo, @soPerson, @soEndUser, 
					@soBkDate,@partNo, @custName, @custRegion, @custRepOffice, @quarter,@year,@month)
				end
				--else if  @month =11 or @month = 12
				else if  @month >=10 or @month <= 12
				begin
					set @quarter = 4
					--set @month = 1
					set @month = 12
					--set @year = convert(int,DATEPART(yyyy,dateadd(m,12,@soBkDate)))
					print 'inserting'
					print @year
					insert into @results values(@lineItmNo, @productID, @coolQty,@coolUnitCost, @coolAmount,@coolReqDate, @coolBkDate, 
					@coolSchdDate, @coolRevDate,@coolStatus,@soNo, @custID,@soPoNo, @soPerson, @soEndUser, 
					@soBkDate,@partNo, @custName, @custRegion, @custRepOffice, @quarter,@year,@month)
				end
				/*
				else if  @month =1
				begin
					print 'in 1'
					set @quarter = 4

					insert into @results values(@lineItmNo, @productID, @coolQty,@coolUnitCost, @coolAmount,@coolReqDate, @coolBkDate, 
					@coolSchdDate, @coolRevDate,@coolStatus,@soNo, @custID,@soPoNo, @soPerson, @soEndUser, 
					@soBkDate,@partNo, @custName, @custRegion, @custRepOffice, @quarter,@year,@month)
				end
				*/
			end
			else
			begin
				--default insert
				set @quarter = null
				set @month = null
				set @year = null
				insert into @results values(@lineItmNo, @productID, @coolQty,@coolUnitCost, @coolAmount,@coolReqDate, @coolBkDate, 
					@coolSchdDate, @coolRevDate,@coolStatus,@soNo, @custID,@soPoNo, @soPerson, @soEndUser, 
					@soBkDate,@partNo, @custName, @custRegion, @custRepOffice, @quarter,@year,@month)
			end
		set @lineItmNo = null 
		set @productID=null 
		set @coolQty=null 
		set @coolUnitCost=null 
		set @coolAmount=null
		set @coolReqDate=null 
		set @coolBkDate=null 
		set @coolSchdDate=null 
		set @coolRevDate=null
		set @coolStatus=null
		set @soNo=null
		set @custID=null
		set @soPoNo=null
		set @soPerson=null
		set @soEndUser=null 
		set @soBkDate=null
		set @partNo=null
		set @custName=null
		set @custRegion=null
		set @custRepOffice=null
		set @quarter=null
		set @year=null
		set @month=null
		fetch next from mainCursor into 
		@lineItmNo, @productID, @coolQty,@coolUnitCost, @coolAmount,@coolReqDate, @coolBkDate, 
		@coolSchdDate, @coolRevDate,@coolStatus,@soNo, @custID,@soPoNo, @soPerson, @soEndUser, 
		@soBkDate,@partNo, @custName, @custRegion, @custRepOffice 
		end		
		close mainCursor
		deallocate mainCursor


	select * from @results order by Yr, Mth
end
